package dao.impl;

import dao.GoodsDao;
import pojo.Goods;
import service.GoodsClassService;
import service.impl.GoodsClassServiceImpl;
import util.BaseDao;
import util.PageBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 实现商品增删改查的实现类
 * @author 周超
 */
public class GoodsDaoImpl implements GoodsDao {

    /**
     * 新增商品
     * @param goods
     * @return
     */
    @Override
    public int addGoods(Goods goods) {
        return BaseDao.cud("insert into goods values(sequence_goods.nextval,?,?,?,?,?,?,?,?,?,?,?,?)",
                goods.getGoName(),goods.getGoSalesVolume(),
                goods.getGoPraise(),goods.getGoUptime(),goods.getGoPictureUrl(),
                goods.getGoDescribe(),goods.getGoCost(),goods.getGoPrice(),
                goods.getGoStock(),goods.getGoState(),goods.getClId(),goods.getGoSecKill());

    }

    /**
     * 添加主键新增商品
     * @param goods
     * @return
     */
    @Override
    public int addGoods2(Goods goods) {
        return BaseDao.cud("insert into goods values(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                goods.getGoId(),goods.getGoName(),goods.getGoSalesVolume(),
                goods.getGoPraise(),goods.getGoUptime(),goods.getGoPictureUrl(),
                goods.getGoDescribe(),goods.getGoCost(),goods.getGoPrice(),
                goods.getGoStock(),goods.getGoState(),goods.getClId(),goods.getGoSecKill());
    }

    /**
     * 删除商品
     * @param goId
     * @return
     */
    @Override
    public int delGoods(int goId) {
        return BaseDao.cud("delete from goods where go_id="+goId);
    }


    /**
     * 修改商品信息
     * @param goods
     * @return
     */
    @Override
    public int altGoods(Goods goods) {
        return BaseDao.cud("update goods set go_name=?,go_salesvolume=?," +
                "go_praise=?,go_uptime=?,go_pictureurl=?,go_describe=?," +
                "go_cost=?,go_price=?,go_stock=?,go_state=?,cl_id=?," +
                "go_seckill=? where go_id=?",goods.getGoName(),goods.getGoSalesVolume(),
                goods.getGoPraise(),goods.getGoUptime(),goods.getGoPictureUrl(),
                goods.getGoDescribe(),goods.getGoCost(),goods.getGoPrice(),goods.getGoStock(),
                goods.getGoState(),goods.getClId(),goods.getGoSecKill(),goods.getGoId());
    }


    /**
     * 按条件模糊查询所有商品信息
     * @param pb
     * @param name
     * @return
     */
    @Override
    public PageBean queryAllGoodsInfo(PageBean pb, String name) {

        List<Goods> list=new ArrayList<Goods>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String s=null;
        if(""!=name&&null!=name){
            s=" 1=1 and go_name like '%"+name+"%' and ";
        }
        else{
            s="";
        }

        String sql=" select * from (\n" +
                "    select rownum r,g.*,cl_name from goods g,goodsclass c " +
                "    where "+s+" g.cl_id=c.cl_id and rownum<=?\n" +
                "    )where r>?";
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            ps.setInt(1,pb.getPageSize()*pb.getPageIndex());
            ps.setInt(2,pb.getPageSize()*(pb.getPageIndex()-1));
            rs=ps.executeQuery();
            while(rs.next()){
                Goods goods=new Goods();
                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                goods.setClName(rs.getString("cl_name"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
                list.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        pb.setList(list);
        return pb;
    }




    /**
     * 模糊查询数据库中有多少条符合条件的商品数据
     * @param name
     * @return
     */
    @Override
    public int count(String name) {
        int count=0;
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String s=null;
        if(null!=name&&""!=name){
            s=" 1=1 and go_name like '%"+name+"%'";
        }
        else{
            s=" 1=1";
        }
        String sql="select count(*) from goods where "+ s;
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            //给占位符号进行赋值
            rs=ps.executeQuery();
            while(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return count;
    }

    /**
     * 查询所有商品信息
     * @return
     */
    @Override
    public List<Goods> queryAllGoods() {
        List<Goods> list=new ArrayList<Goods>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from goods";
        conn=BaseDao.getConnection();
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Goods goods=new Goods();
                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                //goods.setClName(rs.getString("cl_name"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
                list.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return list;
    }

    /**
     * 根据商品id查询商品信息
     * @param goId
     * @return
     */
    @Override
    public Goods queryGoodsInfo(int goId) {

        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from goods where go_id="+goId;
        conn=BaseDao.getConnection();
        Goods goods=new Goods();
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){

                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                //goods.setClName(rs.getString("cl_name"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(rs, ps, conn);
        }
        return goods;
    }

}
